package cc.idoone.spring_boot_hello.jdbc.dao;

import cc.idoone.spring_boot_hello.jdbc.jpa.RoleBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;

/**
 * JdbcTemplate 测试
 *
 * @auther hemj
 * @since 2017/9/23
 */
@Repository
public class RoleDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public List<RoleBean> findAll() {
		return jdbcTemplate.query("select role_id, role_name from zt_role", new RoleRowMapper());
	}

	public RoleBean findRoleById(String id) {
		return jdbcTemplate.queryForObject("select role_id, role_name from zt_role where role_id=?", new Object[]{id}, new RoleRowMapper());
	}

	public RoleBean create(final RoleBean role) {
		final String sql = "insert into zt_role(role_id,role_name) values(?,?)";

		KeyHolder holder = new GeneratedKeyHolder();

		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, role.getRoleId());
				ps.setString(2, role.getRoleName());
				return ps;
			}
		}, holder);

		// String newId = holder.getKey().toString();
		// role.setRoleId(newId);
		return role;
	}

	public void update(final RoleBean role) {
		jdbcTemplate.update(
				"update zt_role set role_name=? where role_id=?",
				new Object[]{role.getRoleName(), role.getRoleId()});
	}

	public void delete(final String id) {
		final String sql = "delete from zt_role where role_id=?";
		jdbcTemplate.update(sql,
				new Object[]{id},
				new int[]{Types.VARCHAR});
	}

}

class RoleRowMapper implements RowMapper<RoleBean> {

	@Override
	public RoleBean mapRow(ResultSet rs, int rowNum) throws SQLException {
		RoleBean RoleBean = new RoleBean();
		RoleBean.setRoleId(rs.getString("role_id"));
		RoleBean.setRoleName(rs.getString("role_name"));

		return RoleBean;
	}
}
